<?php
class Admin_Model_Roles extends Application_Model_Db_Db
{
    public function getAllRoles() {
        $sql = "SELECT
                    `role`.`id`,                    
                    `role`.`role`,
                    GROUP_CONCAT(IFNULL(`department`.`name`, 'Отсутствует')) AS `department_name`
                FROM `role`
                LEFT JOIN `role_department`
                    ON `role`.`id` = `role_department`.`role_id`
                LEFT JOIN `department`
                    ON `role_department`.`department_id` = `department`.`id`
                GROUP BY `role`.`id`,
                         `role`.`role`";
        return $this->_db->fetchAll($sql);
    }

    public function getRoleById($roleId) {
        $sql = "SELECT
                    `id`,
                    `role`
                FROM `role`
                WHERE `id` = ?";
        return $this->_db->fetchRow($sql, $roleId);
    }

    public function getRoleDepartments($roleId) {
        $sql = "SELECT
                    `role_department`.`department_id` AS `id`,
                    IFNULL(`department`.`name`, 'Без кафедры') AS `name`
                FROM `role_department`
                LEFT JOIN `department`
                    ON `role_department`.`department_id` = `department`.`id`
                WHERE `role_department`.`role_id` = ?";
        return $this->_db->fetchAll($sql, $roleId);
    }

    public function getRoleCategoryTypes($roleId) {
        $sql = "SELECT
                    `type`.`id`,
                    `type`.`type`
                FROM `role_category_types`
                INNER JOIN `category_type` AS `type`
                    ON `role_category_types`.`category_type_id` = `type`.`id`
                WHERE `role_category_types`.`role_id` = ?";
        return $this->_db->fetchAll($sql, $roleId);
    }

    public function roleIsExists($roleName) {
        $sql = "SELECT
                    `id`
                FROM `role`
                WHERE `role` = '{$roleName}'";
        return $this->_db->fetchOne($sql);
    }

    public function saveRole($roleName) {
        $this->_db->insert('role', array('role' => $roleName));
        return $this->_db->lastInsertId();
    }

    public function updateRole($roleName, $roleId) {
        $where = 'id = ' . $roleId;
        $this->_db->update('role', array('role' => $roleName), $where);     
    }

    public function deleteRole($roleId) {
        $this->_db->delete('role', 'id = ' . $roleId);
    }

    public function saveRoleCategories($data) {
        $this->_db->insert('role_categories', $data);
    }

    public function deleteRoleCategories($roleId) {
        $this->_db->delete('role_categories', 'role_id = ' . $roleId);
    }

    public function saveRoleCategoryTypes($data) {
        $this->_db->insert('role_category_types', $data);
    }

    public function deleteRoleCategoryTypes($roleId) {
        $this->_db->delete('role_category_types', 'role_id = ' . $roleId);
    }

    public function saveRoleDepartments($data) {
        $this->_db->insert('role_department', $data);
    }

    public function deleteRoleDepartments($roleId) {
        $this->_db->delete('role_department', 'role_id = ' . $roleId);
    }

    public function saveRoleResources($data) {
        $this->_db->insert('role_privileges', $data);
    }

    public function deleteRoleResources($roleId) {
        $this->_db->delete('role_privileges', 'role_id = ' . $roleId);
    }
}